Recommendations for SQL Server Optimizations
Configure settings as follows to tune performance.
Operating System Settings
- Set Power Mode (Control Panel) to "High Performance"
- Set System Properties to "Adjust for Best Performance"
- Configure PageFile.sys size should be 1.5 * Total Memory and located on SSD if possible
- Add SQL User to Security for Perform Volume Maintenance Task
AntiVirus Software Exclusions
- SQL Server Log Files: exclude the LOG folder for each SQL instance
- SQL Server DB files: exclude *.MDF, *.LDF, *.NDF
- SQL Trace Files: exclude *.TRC
- Pagefile.sys
- SQL Server Back-up Files (i.e., *.bak, *.trn)
Server level settings
NOTE: To access, right-click Server in SSMS > Properties.
- Set min server memory to 8GB (8192 MB)
- Set max server memory (based on total Operating System reported memory - 4GB
- Set Cost Threshold to 75
- Set Max Degree of Parallelism to 4
- Set Backup Compression to On
Database Level Settings
Configure System Model database using the following settings:
- Size Each Data File to 512 MB, 512 MB Growth
- Size Log File to 64 MB, 256 MB Growth
- Set Read Committed Snapshot Isolation to True
- Set Recovery Mode to Simple
NOTE: It is important that this step be executed BEFORE the creation of the CranSoft database, and the execution of the DSP install file. And new DB that is created reads these default settings from the Model database, so it avoids incorrect DB settings.
TempDB Optimization
Configure the TempDB system database to these settings, preferably on SSD:
- 8 Files - Each sized at 8GB (8192 MB), 512 MB Growth
- Log File set to 512 MB, 512 MB Growth
Other SQL Server settings
NOTE: Consult with the Database Administrator for further details.
Go to Programs > SQL Server Configuration Manager > SQL Server Services > right click on SQL Server service > Properties > Startup Parameters > input “-T174” in Specify Startup Parameter > Apply (for each trace flag to add)
- Add Trace Flag 174
- Add Trace Flag 1117 and 1118 if version lower than SQL Server 2016
- Set all User Databases to AUTOGROW_ALL_FILES (Only for SQL 2016 +)
- Set up Weekly Index Maintenance Job
- Set up Weekly Statistics Rebuild Job (or more often as required)
- Set up Nightly Database Backup Job